# -*- coding: utf-8 -*-
"""
Module Description: peewee orm接口
Date: 2018/8/2
Author:Bai Jin Ping
"""
import copy
import peewee
from framework.dal.models import get_database_pool
from framework.dal.dbmodel import all_db_models

from framework.dal.transaction import TransactionPropagation


def _expressions(mdl_cls, conditions):
    """
    构造查询筛选参数列表
    """
    expressions = []
    for field_opt, val in conditions.iteritems():
        field, opt = field_opt.rsplit('__')

        # 如果in的元素是空集,则返回None
        if opt == 'in':
            if not val:
                return

        # 支持eq, lt, lte, gt, gte, ne, in, is, isnot, like
        pw_opt = peewee.DJANGO_MAP.get(opt)
        # 增加isnot支持
        if opt == 'isnot':
            pw_opt = peewee.OP.IS_NOT
        if not pw_opt \
                or pw_opt in ['ilike', 'regexp']:  # 不支持的方法
            raise NotImplementedError('Unsupported filter condition:(%s)' % opt)

        # peewee在连接sqlite时, 模糊匹配like使用的GLOB语句而不是LIKE, 因此是以*作为匹配符, 而不是%
        if pw_opt == peewee.OP.LIKE:
            if isinstance(mdl_cls._meta.database, peewee.SqliteDatabase):
                val = val.replace('%', '*')

            # 增加peewee的代理模式
            elif isinstance(mdl_cls._meta.database, peewee.Proxy):
                if isinstance(getattr(mdl_cls._meta.database, 'obj'), peewee.SqliteDatabase):
                    val = val.replace('%', '*')

        exp = peewee.Expression(
            lhs=getattr(mdl_cls, field),
            op=pw_opt,
            rhs=val
        )
        expressions.append(exp)
    return expressions


def _get_mdl_cls_by_name(mdl_name):
    """返回对应的Peewee Model"""
    return filter(lambda mdl_cls: mdl_cls.__name__ == mdl_name, all_db_models)[0]


def is_sqlite_mode():
    """是否是sqlite模式"""
    db = get_database_pool()
    if isinstance(db, peewee.SqliteDatabase):
        return True

    # 代理模式初始化sqlite实例
    elif isinstance(db, peewee.Proxy):
        if isinstance(getattr(db, 'obj'), peewee.SqliteDatabase):
            return True

    return False


def transaction_sql_placeholder():
    """
    返回生成的sql使用的参数占位符
    :return:
    """
    # sqlite使用?作为参数占位符
    # mysql-python/pymysql使用%s
    return '?' if is_sqlite_mode() else '%s'


def _update_batch_insert_value_by_default_value(mdl_name, insert_values):
    """
    使用peewee建表(通常是单元测试)时,最终表结构缺少DEFAULT,并且是NOT NULL字段.
    因此当插入记录时,需要传入模型中设定的默认值,避免发生字段非空报错.
    使用单模型插入时,peewee已经自带这个逻辑.
    但批量插入时,需要自行处理.
    :param mdl_name: 数据表名
    :param insert_values: list(dict), 批量插入的数据字典列表
    :return: 修改后的insert_values
    """
    insert_values = copy.deepcopy(insert_values)

    peewee_mdl_cls = _get_mdl_cls_by_name(mdl_name)
    mdl_default_dict = peewee_mdl_cls._meta._default_by_name
    for val in insert_values:
        for default_k, default_v in mdl_default_dict.iteritems():
            if default_k not in val:
                val[default_k] = default_v

    return insert_values


def _parse_select_result(mdl_cls, select_result):
    """
    对查询结果进一步处理以适配dal.orm的内部类型
    :param mdl_cls: peewee model类
    :param select_result: 查询结果是已经取_data之后得到的list(dict)格式
    """
    if not select_result:
        return

    ######################
    # 转换datetime和date类型为字符串格式
    ######################
    dt_fields = []
    for field_name, field_obj in mdl_cls._meta.fields.iteritems():
        if isinstance(field_obj, (peewee.DateTimeField, peewee.DateField)):
            dt_fields.append(field_name)

    for rcd in select_result:
        for field_name in dt_fields:
            if field_name in rcd and rcd[field_name] is not None:
                rcd[field_name] = str(rcd[field_name])


def insert_model(db_name, mdl_cls, values, trace_id=None):
    """插入新记录"""
    values = _update_batch_insert_value_by_default_value(mdl_cls.__name__, values)
    return _get_mdl_cls_by_name(mdl_cls.__name__).insert_many(values).execute()


def delete_model(db_name, mdl_cls, conditions, operator="AND", trace_id=None):
    """
    @return: {"code": int, "info": string}
    @ not_sql提供占位符,让服务端判断是否要执行sql语句
    """
    pw_mdl_cls = _get_mdl_cls_by_name(mdl_cls.__name__)
    delete_query = pw_mdl_cls.delete()
    expressions = _expressions(pw_mdl_cls, conditions)
    if expressions is None:
        return 0
    if not expressions:
        return delete_query.execute()
    else:
        return delete_query.where(*expressions).execute()


def update_model(db_name, model_name, pks, values, conditions, operator="AND", trace_id=None):
    """
    @return: {"code": int, "info": string}
    @ not_sql提供占位符,让服务端判断是否要执行sql语句
    """
    pw_mdl_cls = _get_mdl_cls_by_name(model_name)
    update_query = pw_mdl_cls.update(**values)
    expressions = _expressions(pw_mdl_cls, conditions)
    if expressions is None:
        return 0
    if not expressions:
        return update_query.execute()
    else:
        return update_query.where(*expressions).execute()


def select_model(db_name, model_name, pks, conditions, columns=None,
                 operator="AND", order=None, limit=None, offset=0, trace_id=None):
    """
    @return: {"code": int, "info": string}
    @ not_sql提供占位符,让服务端判断是否要执行sql语句
    """
    pw_mdl_cls = _get_mdl_cls_by_name(model_name)

    # 筛选列
    if columns is None:
        clns = []
    else:
        clns = [getattr(pw_mdl_cls, cln) for cln in columns]
    select_obj = pw_mdl_cls.select(*clns)

    # 排序
    if order:
        order_clauses = []
        for order_field in order.split(','):
            # ASC field
            if order_field.endswith('ASC'):
                clause = getattr(pw_mdl_cls, order_field[:-5]).asc()
            # DESC field
            else:
                clause = getattr(pw_mdl_cls, order_field[:-6]).desc()
            order_clauses.append(clause)

        select_obj = select_obj.order_by(*order_clauses)

    # limit, offset
    if limit:
        select_obj = select_obj.limit(limit)
    if offset:
        select_obj = select_obj.offset(offset)

    # 查询条件
    expressions = _expressions(pw_mdl_cls, conditions)

    if expressions is None:
        return []

    select_result = select_obj if not expressions else select_obj.where(*expressions)
    result_list = [rcd._data for rcd in select_result]

    # 进一步处理查询结果
    _parse_select_result(pw_mdl_cls, result_list)

    return result_list


def select_count(db_name, mdl_cls, conditions, trace_id):
    """
    返回记录数
    """
    pw_mdl_cls = _get_mdl_cls_by_name(mdl_cls.__name__)
    expressions = _expressions(pw_mdl_cls, conditions)
    if expressions is None:
        return 0

    select_obj = pw_mdl_cls.select() if not expressions else pw_mdl_cls.select().where(*expressions)

    from framework.dal.orm.controller_api import AggregateCount
    return [{
        AggregateCount.get_column_name(): select_obj.aggregate(peewee.fn.Count('*'))
    }]


def select_min(db_name, mdl_cls, field_name, conditions, trace_id):
    """
    返回最小值
    """
    pw_mdl_cls = _get_mdl_cls_by_name(mdl_cls.__name__)
    expressions = _expressions(mdl_cls, conditions)
    if expressions is None:
        return 0

    select_obj = pw_mdl_cls.select() if not expressions else pw_mdl_cls.select().where(*expressions)

    from framework.dal.orm.controller_api import AggregateMin
    return [{
        AggregateMin.get_column_name(field_name): select_obj.aggregate(peewee.fn.Min(getattr(pw_mdl_cls, field_name)))
    }]


def select_max(db_name, mdl_cls, field_name, conditions, trace_id):
    """
    返回最大值
    """
    pw_mdl_cls = _get_mdl_cls_by_name(mdl_cls.__name__)
    expressions = _expressions(pw_mdl_cls, conditions)
    if expressions is None:
        return 0

    select_obj = pw_mdl_cls.select() if not expressions else pw_mdl_cls.select().where(*expressions)

    from framework.dal.orm.controller_api import AggregateMax
    return [{
        AggregateMax.get_column_name(field_name): select_obj.aggregate(peewee.fn.Max(getattr(pw_mdl_cls, field_name)))
    }]


def transaction_model(trans_obj, work, trace_id=None):
    """
    事务
    url: /[db]
    body: [
        {"orm": "对象名", "pk": "主键名", "action": "insert", "body": {"字段名": 字段值, ...}},
        {"orm": "对象名", "pk": "主键名", "action": "delete", "body": {"字段名__运算符": 字段值, ...}},
        {"orm": "对象名", "pk": "主键名", "action": "update", "body": {"values": {"字段名": 字段值, ...},
        "conditions": {"字段名__运算符": 字段值, ..., "operator": "AND[OR]"}},
        ...
        ]
    @ not_sql提供占位符,让服务端判断是否要执行sql语句
    @return: {"code": int, "info": string}
    """
    from framework.dal.transaction_sql_generate import generate_sql_from_transaction_change_list
    database = get_database_pool()

    if trans_obj.propagation is TransactionPropagation.REQUIRED:
        peewee_transaction = database.transaction()
    elif trans_obj.propagation is TransactionPropagation.REQUIRES_NEW:
        peewee_transaction = database.atomic()
    else:
        raise ValueError('Unsupported Transaction Propagation!')

    # 事务中如果有批量插入操作,需要添加默认值
    for trans in work:
        if trans['action'] == 'insert':
            if isinstance(trans['body']['values'], list):
                trans['body']['values'] = _update_batch_insert_value_by_default_value(
                    mdl_name=trans['orm'],
                    insert_values=trans['body']['values']
                )

    sql_list, param_list = generate_sql_from_transaction_change_list(work, placeholder=transaction_sql_placeholder())
    assert len(sql_list) == len(param_list)

    if is_sqlite_mode():
        new_param_list = []
        for params in param_list:
            new_param_list.append([unicode(e) if isinstance(e, basestring) and isinstance(e, str) else e for e in params])
        param_list = new_param_list

    try:
        with peewee_transaction:
            for idx, sql in enumerate(sql_list):
                params = param_list[idx]
                peewee_transaction.db.execute_sql(sql, params)
        return True
    except Exception as e:
        if trans_obj.propagation is TransactionPropagation.REQUIRES_NEW:
            peewee_transaction._helper.rollback(begin=False)
        else:
            peewee_transaction.rollback(begin=False)
        raise


def query_sql(db_name, sql, trace_id):
    """
    执行sql语句
    """
    db = get_database_pool()
    if is_sqlite_mode():
        conn = db.get_conn()
        cursor = conn.cursor()
        cursor.execute(sql)
        res = cursor.fetchall()
        result = []
        for row in res:
            rcd = {}
            for idx, col in enumerate(cursor.description):
                rcd[col[0]] = row[idx]
            result.append(rcd)
        return result

    else:
        from pymysql.cursors import DictCursor
        conn = db.get_conn()
        with conn.cursor(DictCursor) as cursor:
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
